package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import util.DBUtil;
import model.Shape;
import model.Stop;
import model.SubRota;

public class DaoAgente {
	
	//busca stops de uma viagem	(trip) 
	public ArrayList<Stop> getStopsOfTrips(String trip_id){ 
	
		
		// Monta base do SQL
		String sql = "(SELECT stops.stop_id, stops.stop_lat, stops.stop_lon, stops.stop_name, stops.stop_desc, stop_times.stop_sequence ";
		sql += "FROM stops, stop_times, trips "; 
		sql += "WHERE trips.trip_id = stop_times.trip_id ";
		sql += "AND stop_times.stop_id = stops.stop_id ";
		sql += "AND trips.trip_id = '"+trip_id+"')";
		ArrayList<Stop> stops = new ArrayList<Stop>();
		
		try {
			// Recupera conexão com o banco
			DBUtil db = DBUtil.getInstance();
			Connection con = db.getConnection();
						
			// Cria um statement
			Statement stmt = con.createStatement();
			
			// Executa o sql
			ResultSet rs = stmt.executeQuery(sql);
			
			
			while (rs.next()) {
				Stop st = new Stop();
				st.setStop_id(rs.getInt("stop_id"));
				st.setStop_lat(rs.getFloat("stop_lat"));
				st.setStop_lon(rs.getFloat("stop_lon"));
				st.setStop_name(rs.getString("stop_name"));
				st.setStop_desc(rs.getString("stop_desc"));
				st.setStop_sequence(rs.getInt("stop_sequence"));
				
				stops.add(st);
			}
			rs.close();
			stmt.close();
			
			return stops;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	//busca stops de uma viagem	(trip) entre duas stop sequences
	public ArrayList<Stop> getStopsOfTrips(String trip_id, int stSeqIni, int stSeqFim){ 
		
			
			// Monta base do SQL
		String sql = "(SELECT stops.stop_id, stops.stop_lat, stops.stop_lon, stops.stop_name, stops.stop_desc, stop_times.stop_sequence ";
		sql += "FROM stops, stop_times, trips "; 
		sql += "WHERE trips.trip_id = stop_times.trip_id ";
		sql += "AND stop_times.stop_id = stops.stop_id ";
		sql += "AND trips.trip_id = '"+trip_id+"' ";
		sql += "AND stop_sequence >= '"+stSeqIni+"' AND stop_sequence <= '"+stSeqFim+"')";
			ArrayList<Stop> stops = new ArrayList<Stop>();
			
		try {
			// Recupera conexão com o banco
			DBUtil db = DBUtil.getInstance();
			Connection con = db.getConnection();
						
			// Cria um statement
			Statement stmt = con.createStatement();
			
			// Executa o sql
			ResultSet rs = stmt.executeQuery(sql);
			
			
			while (rs.next()) {
				Stop st = new Stop();
				st.setStop_id(rs.getInt("stop_id"));
				st.setStop_lat(rs.getFloat("stop_lat"));
				st.setStop_lon(rs.getFloat("stop_lon"));
				st.setStop_name(rs.getString("stop_name"));
				st.setStop_desc(rs.getString("stop_desc"));
				st.setStop_sequence(rs.getInt("stop_sequence"));
				
				stops.add(st);
			}
			rs.close();
			stmt.close();
			
			return stops;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	

	//busca stops em um raio	
	public ArrayList<Stop> getStopsOfCoordRaio(double Latitude, double longitude, int multiplicadorRaio){ 
	
		
		// Monta base do SQL
		String sql = "SELECT * ";
		sql += "FROM stops ";
		sql += "WHERE (stops.stop_lon - ("+longitude+"))^2 + (stops.stop_lat - ("+Latitude+"))^2 <= "+(multiplicadorRaio * 0.0045)+"^2;";
		ArrayList<Stop> stops = new ArrayList<Stop>();
		
		try {
			// Recupera conexão com o banco
			DBUtil db = DBUtil.getInstance();
			Connection con = db.getConnection();
						
			// Cria um statement
			Statement stmt = con.createStatement();
			
			// Executa o sql
			ResultSet rs = stmt.executeQuery(sql);
			
			
			while (rs.next()) {
				Stop st = new Stop();
				st.setStop_id(rs.getInt("stop_id"));
				st.setStop_lat(rs.getFloat("stop_lat"));
				st.setStop_lon(rs.getFloat("stop_lon"));
				st.setStop_name(rs.getString("stop_name"));
				st.setStop_desc(rs.getString("stop_desc"));
				stops.add(st);
			}
			rs.close();
			stmt.close();
			
			return stops;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}	
	}



	//busca stops em um ratio passando a linha
	public ArrayList<Stop> getStopsOfCoordRaio(String viagem, double Latitude, double longitude, int multiplicadorRaio){ 
		
			
			// Monta base do SQL
		String sql = "(SELECT stops.stop_id, stop_lat, stop_lon, stop_name, stop_desc, stop_times.stop_sequence "; 
		sql += "FROM trips, stops, stop_times ";
		sql += "WHERE trips.trip_id = '"+viagem+"' ";
		sql += "AND trips.trip_id = stop_times.trip_id ";
		sql += "AND stop_times.stop_id = stops.stop_id ";
		sql += "AND (stops.stop_lon - ("+longitude+"))^2 + (stops.stop_lat - ("+Latitude+"))^2 <= "+(multiplicadorRaio * 0.0045)+"^2)";
		ArrayList<Stop> stops = new ArrayList<Stop>();
			
			try {
				// Recupera conexão com o banco
				DBUtil db = DBUtil.getInstance();
				Connection con = db.getConnection();
							
				// Cria um statement
				Statement stmt = con.createStatement();
				
				// Executa o sql
				ResultSet rs = stmt.executeQuery(sql);
				
				while (rs.next()) {
					Stop st = new Stop();
					st.setStop_id(rs.getInt("stop_id"));
					st.setStop_lat(rs.getFloat("stop_lat"));
					st.setStop_lon(rs.getFloat("stop_lon"));
					st.setStop_name(rs.getString("stop_name"));
					st.setStop_desc(rs.getString("stop_desc"));
					st.setStop_sequence(rs.getInt("stop_sequence"));
					stops.add(st);
				}
				rs.close();
				stmt.close();
				
				return stops;
				
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
	}
	

	
	
	//busca stop passando viagem (trip) e stop_id
	public Stop getStopOfTripStopId(String viagem, int stop_id){ 
		
			
			// Monta base do SQL
		String sql = "(SELECT stops.stop_id, stops.stop_lat, stops.stop_lon, stops.stop_name, stops.stop_desc, stop_times.stop_sequence "; 
		sql += "FROM stops, stop_times, trips ";
		sql += "WHERE trips.trip_id = stop_times.trip_id "; 
		sql += "AND stop_times.stop_id = stops.stop_id ";
		sql += "AND trips.trip_id = '"+viagem+"' ";
		sql += "AND stops.stop_id = "+stop_id+")";
		
		
			
			try {
				// Recupera conexão com o banco
				DBUtil db = DBUtil.getInstance();
				Connection con = db.getConnection();
							
				// Cria um statement
				Statement stmt = con.createStatement();
				
				// Executa o sql
				ResultSet rs = stmt.executeQuery(sql);
				
				Stop st = new Stop();				
				while (rs.next()) {

					st.setStop_id(rs.getInt("stop_id"));
					st.setStop_lat(rs.getFloat("stop_lat"));
					st.setStop_lon(rs.getFloat("stop_lon"));
					st.setStop_name(rs.getString("stop_name"));
					st.setStop_desc(rs.getString("stop_desc"));
					st.setStop_sequence(rs.getInt("stop_sequence"));
				}
				rs.close();
				stmt.close();
				
				return st;
				
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
	}




	//busca linhas coincidentes de um Ponto de origem a um ponto de destino buscando de um raio x do ponto de destino
	public ArrayList<String> getTripsOfStopCoordRaio(int idStop, double destinoLatitude, double destinoLongitude, int multiplicadorRaio){
		
				// Monta base do SQL
				
				//origem
				String sql = "(SELECT trips.trip_id ";
				sql += "FROM trips, stops, stop_times ";	
				sql += "WHERE trips.trip_id = stop_times.trip_id ";		
				sql += "AND stop_times.stop_id = stops.stop_id ";
				sql += "AND stops.stop_id = "+idStop+") ";
				sql += "INTERSECT ";
				//destino
				sql +=  "(SELECT trips.trip_id "; 
				sql += "FROM trips, stops, stop_times ";
				sql += "WHERE trips.trip_id = stop_times.trip_id ";
				sql += "AND stop_times.stop_id = stops.stop_id ";
				sql += "AND (stops.stop_lon - ("+destinoLongitude+"))^2 + (stops.stop_lat - ("+destinoLatitude+"))^2 <= "+(multiplicadorRaio * 0.0045)+"^2)";		
						
				ArrayList<String> trips = new ArrayList<String>();
				
				try {
					// Recupera conexão com o banco
					DBUtil db = DBUtil.getInstance();
					Connection con = db.getConnection();
								
					// Cria um statement
					Statement stmt = con.createStatement();
					
					// Executa o sql
					ResultSet rs = stmt.executeQuery(sql);
					
					
					while (rs.next()) {
						String trip_id = new String();
						trip_id = rs.getString("trip_id");
						trips.add(trip_id);
					}
					rs.close();
					stmt.close();
					
					return trips;
					
				} catch (SQLException e) {
					e.printStackTrace();
					return null;
				}
		
	}
	
	
	//=============Busca shapes de uma viagem, passano a viagem e cordnadas de um ponto (raio de busca de 30m)
	public ArrayList<Shape> getShapesOfTripCoordRaio(String idTrip, double latitude, double longitude, int raio){
		
			// Monta base do SQL
			String sql = "SELECT shapes.shape_id, shapes.shape_pt_lat, shapes.shape_pt_lon, shapes.shape_pt_sequence, shapes.shape_dist_traveled ";
			sql += "FROM  shapes, trips ";
			sql += "WHERE trips.shape_id = shapes.shape_id ";
			sql += "AND trips.trip_id = '"+idTrip+"' ";
			sql += "AND (shapes.shape_pt_lon - ("+longitude+"))^2 + (shapes.shape_pt_lat - ("+latitude+"))^2 <= ("+raio+" * 0.00027)^2;";

		
					
			ArrayList<Shape> shapes = new ArrayList<Shape>();
			
			try {
				// Recupera conexão com o banco
				DBUtil db = DBUtil.getInstance();
				Connection con = db.getConnection();
							
				// Cria um statement
				Statement stmt = con.createStatement();
				
				// Executa o sql
				ResultSet rs = stmt.executeQuery(sql);
				
				
				while (rs.next()) {
					Shape sp = new Shape();
					sp.setShape_id(rs.getInt("shape_id"));
					sp.setShape_pt_lat(rs.getFloat("shape_pt_lat"));
					sp.setShape_pt_lon(rs.getFloat("shape_pt_lon"));
					sp.setShape_pt_sequence(rs.getInt("shape_pt_sequence"));
					sp.setShape_dist_traveled(rs.getDouble("shape_dist_traveled"));
					shapes.add(sp);
				}
				rs.close();
				stmt.close();
				
				return shapes;
				
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}

	}	
	

	

	public ArrayList<Shape> getShapesOfTripOrigemDestino(int shapeId, int sequenciaOrigem, int sequenciaDestino){
		
			// Monta base do SQL
			String sql = "SELECT * "; 
			sql += "FROM  shapes "; 
			sql += "WHERE shape_id = "+shapeId+" ";  
			sql += "AND (shape_pt_sequence >= "+sequenciaOrigem+" AND shape_pt_sequence <= "+sequenciaDestino+");"; 
	
		
					
			ArrayList<Shape> shapes = new ArrayList<Shape>();
			
			try {
				// Recupera conexão com o banco
				DBUtil db = DBUtil.getInstance();
				Connection con = db.getConnection();
							
				// Cria um statement
				Statement stmt = con.createStatement();
				
				// Executa o sql
				ResultSet rs = stmt.executeQuery(sql);
				
				
				while (rs.next()) {
					Shape sp = new Shape();
					sp.setShape_id(rs.getInt("shape_id"));
					sp.setShape_pt_lat(rs.getFloat("shape_pt_lat"));
					sp.setShape_pt_lon(rs.getFloat("shape_pt_lon"));
					sp.setShape_pt_sequence(rs.getInt("shape_pt_sequence"));
					sp.setShape_dist_traveled(rs.getDouble("shape_dist_traveled"));
					shapes.add(sp);
				}
				rs.close();
				stmt.close();
				
				return shapes;
				
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}

	}
	
	
	
	public SubRota preencheSubrota(String trip_id, SubRota srt){
		// Monta base do SQL
		String sql = "SELECT trips.trip_id, routes.route_id, routes.route_long_name, routes.route_color, fare_attributes.fare_id, fare_attributes.price ";
		sql += "FROM routes, trips, fare_attributes, fare_rules ";
		sql += "WHERE trips.route_id = routes.route_id ";
		sql += "AND routes.route_id = fare_rules.route_id ";
		sql += "AND fare_rules.fare_id = fare_attributes.fare_id ";
		sql += "AND trips.trip_id = '"+trip_id+"';";
		
		
			
			try {
				// Recupera conexão com o banco
				DBUtil db = DBUtil.getInstance();
				Connection con = db.getConnection();
							
				// Cria um statement
				Statement stmt = con.createStatement();
				
				// Executa o sql
				ResultSet rs = stmt.executeQuery(sql);
				
			
				while (rs.next()) {

					srt.setViagem(rs.getString("trip_id"));
					srt.setLinha(rs.getString("route_id"));
					srt.setDescricaoLong(rs.getString("route_long_name"));
					srt.setCor(rs.getString("route_color"));
					srt.setTipo(rs.getString("fare_id"));
					srt.setTarifa(rs.getDouble("price"));
				}
				rs.close();
				stmt.close();
				
				return srt;
				
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			}
	}
	
	
	
	

}
